<?php
namespace app\entries\controller;
use think\Controller;
use think\Loader;

/**
 *
 */
class Pinpai extends Logincheck
{
    //查看品牌信息页面
    function pinpai()
    {
        //返回茶类
        $retval = getTeaCase();
        $this->assign('teacase', $retval);

        //返回产区
        $retval = getTeaArea();
        $this->assign('teaarea', $retval);

        return $this->fetch();
    }


    //获取对应参数的品牌信息
    function getChoose($teacase, $teaarea)
    {
        $this->assign('choosetea', $teacase);
        $this->assign('choosearea', $teaarea);
        if($teacase == 10000){
            return $this->redirect('getAll');
        }else if($teacase == 9999){
            return $this->redirect('getOther');
        }else{
            if($teaarea==10000){
                //某茶类全部
                $data = ['teacase_id'=>$teacase];
                $retval = getChoose("pinpai", $data);//参数1为表单名称，$data为查询条件
                $rank = 1;
                $tid_name = teacase_ids();
                $aid_name = teaarea_ids();
                foreach ($retval as &$r) {
                    $r['rank'] = $rank;
                    $rank++;
                    $r['teacase'] = $tid_name[$r['teacase_id']];
                    $r['teaarea'] = $aid_name[$r['teaarea_id']];
                    $areas = db("case_area")->field('teaarea_id')->where("teacase_id",$r['teacase_id'])->select();
                    foreach ($areas as &$a) {
                        $a['area'] = $aid_name[$a['teaarea_id']];
                    }
                    $r['areas'] = $areas;
                }
                $this->assign('data', $retval);
            }else if($teaarea==9999){
                //未上榜
                $data = ['teacase_id'=>$teacase, 'bangdan_id'=>4];
                $retval = getChoose("pinpai", $data);//参数1为表单名称，$data为查询条件
                $rank = 1;
                $tid_name = teacase_ids();
                $aid_name = teaarea_ids();
                foreach ($retval as &$r) {
                    $r['rank'] = $rank;
                    $rank++;
                    $r['teacase'] = $tid_name[$r['teacase_id']];
                    $r['teaarea'] = $aid_name[$r['teaarea_id']];
                    $areas = db("case_area")->field('teaarea_id')->where("teacase_id",$r['teacase_id'])->select();
                    foreach ($areas as &$a) {
                        $a['area'] = $aid_name[$a['teaarea_id']];
                    }
                    $r['areas'] = $areas;
                }
                 $this->assign('data', $retval);
            }else{
                //查询品牌榜单信息
                $data = [
                    'teacase_id'=>$teacase,
                    'teaarea_id'=> $teaarea
                ];
                $retval = getChoose("pinpai", $data);//参数1为表单名称，$data为查询条件
                $rank = 1;
                $tid_name = teacase_ids();
                $aid_name = teaarea_ids();
                foreach ($retval as &$r) {
                    $r['rank'] = $rank;
                    $rank++;
                    $r['teacase'] = $tid_name[$r['teacase_id']];
                    $r['teaarea'] = $aid_name[$r['teaarea_id']];
                    $areas = db("case_area")->field('teaarea_id')->where("teacase_id",$r['teacase_id'])->select();
                    foreach ($areas as &$a) {
                        $a['area'] = $aid_name[$a['teaarea_id']];
                    }
                    $r['areas'] = $areas;
                }
                $this->assign('data', $retval);
            }
        }

        //获取茶类的名称
        $retval = getTeaCaseName($teacase);
        $this->assign('current', $retval);

        //获取产区的名称
        $retval = getTeaAreaName($teaarea);
        $this->assign('currentArea', $retval);

        //获取茶类
        $retval = getTeaCase();
        $this->assign('teacase', $retval);

        //获取产区
        $retval=$this->reTeaArea($teacase);
        $this->assign('teaarea', $retval);

        return $this->fetch("get_all");
    }

    //取所有数据
    function getAll()
    {
        $tid_name = teacase_ids();
        $aid_name = teaarea_ids();
        $rank = 1;
        $retval =  model('Pinpai')->getAllpp();
        foreach ($retval as &$r) {
            $r['rank'] = $rank;
            $rank++;
            $r['teacase'] = $tid_name[$r['teacase_id']];
            $r['teaarea'] = $aid_name[$r['teaarea_id']];
            $areas = db("case_area")->field('teaarea_id')->where("teacase_id",$r['teacase_id'])->select();
            foreach ($areas as &$a) {
                $a['area'] = $aid_name[$a['teaarea_id']];
            }
            $r['areas'] = $areas;
        }
        // print_pre($retval);
        $this->assign('data', $retval);
        //获取茶类
        $retval = getTeaCase();
        $this->assign('teacase', $retval);

        $this->assign('choosetea', 10000);
        $this->assign('choosearea', 10000);
        //获取茶类的名称
        $this->assign('current', '');
        //获取产区的名称
        $this->assign('currentArea', '');
        return $this->fetch();
    }

    //取未上榜
    function getOther()
    {
        $tid_name = teacase_ids();
        $aid_name = teaarea_ids();
        $rank = 1;
        $retval =  model('Pinpai')->getOther();

        foreach ($retval as &$r) {
            $r['rank'] = $rank;
            $rank++;
            $r['teacase'] = $tid_name[$r['teacase_id']];
            $r['teaarea'] = $aid_name[$r['teaarea_id']];
            $areas = db("case_area")->field('teaarea_id')->where("teacase_id",$r['teacase_id'])->select();
            foreach ($areas as &$a) {
                $a['area'] = $aid_name[$a['teaarea_id']];
            }
            $r['areas'] = $areas;
        }
        $this->assign('data', $retval);
        //获取茶类
        $retval = getTeaCase();
        $this->assign('teacase', $retval);
        $this->assign('choosetea', 9999);
        $this->assign('choosearea', 9999);
        //获取茶类的名称
        $this->assign('current', '');
        //获取产区的名称
        $this->assign('currentArea', '');
        return $this->fetch('get_all');
    }

    //修改品牌信息
    function modifyPpai($id,$teacase, $teaarea, $name, $relatedwords, $sale, $company, $pnumber, $contact, $phone, $wechat, $detailintro, $score)
    {
        $tid = ids_teacase();
        $aid = ids_teaarea();
        $data = [
            "name"=>$name,
            "relatedwords"=>$relatedwords,
            // "teacase_id"=>$tid[$teacase],
            "teacase_id"=>$teacase,
            "teaarea_id"=>$aid[$teaarea],
            "sale"=>$sale,
            "company"=>$company,
            "pnumber"=>$pnumber,
            "contact"=>$contact,
            "phone"=>$phone,
            "wechat"=>$wechat,
            "detailintro"=>$detailintro,
            "score"=>$score,
            "updatetime"=>time()
        ];

        $retval = model("Pinpai")->modifyPpai($id, $data);
        return $retval;
    }

    //是否上榜
    function shangbang($id, $bangdan_id)
    {
        $data = ['bangdan_id'=>$bangdan_id];
        $retval = model('Pinpai')->modifyPpai($id, $data);
        return $retval;
    }

    //显示手动添加品牌信息页面
    function addpinpai()
    {
        //获得茶类
        $retval = getTeaCase();
        $this->assign("teacase", $retval);
        //获得产区
        $retval = getTeaArea();
        $this->assign("teaarea", $retval);
        //获得榜单
        $retval = getBangDan();
        $this->assign("bangdan", $retval);

        return $this->fetch();
    }

    //手动增加品牌信息
    function addpp($name, $relatedwords, $teacase, $teaarea, $bangdan, $sale, $company, $pnumber, $contact, $phone, $wechat, $detailintro)
    {
        $established_point = $this->established($detailintro);
        $sale_point = $this->sale($sale);
        $pnumber_point = $this->pnumber($pnumber);
        $random = rand(0,30)*10;
        $score = $established_point + $sale_point +$pnumber_point + $random;

        $data = [
            "name" => $name,
            "relatedwords" => $relatedwords,
            "teacase_id" => $teacase,
            "teaarea_id" => $teaarea,
            "bangdan_id" => $bangdan,
            "sale" => $sale,
            "company" => $company,
            "pnumber" => $pnumber,
            "contact" => $contact,
            "phone" => $phone,
            "wechat" => $wechat,
            "detailintro" => $detailintro,
            "score" => $score,
            "addtime" => time()
        ];

        $retval = model('Pinpai')->addpp($data);
        if($retval == 1){
            return $this->success("添加成功");
        }else{
            return $this->error("添加失败");
        }

    }

    //删除品牌信息
    function delPp($id)
    {
        $retval = model("Pinpai")->delPp($id);
        if($retval != 1){
            return 0;
        }else{
            return 1;
        }
    }

    //从Excel导入品牌信息页面
    function fromExcelpp()
    {
        return $this->fetch();
    }

    //将Excel里的品牌信息导入数据库
    function insertExcelpp()
    {
         if(request() -> isPost())
        {
            Loader::import('PHPExcel.Classes.PHPExcel');
            Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
            Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5');
            //获取表单上传文件
            $file = request()->file('excel');
            $info = $file->validate(['ext' => 'xlsx'])->move(ROOT_PATH . 'public');  //上传验证后缀名,以及上传之后移动的地址  \public 目录下

            if($info)
            {
                //echo $info->getFilename();
                $exclePath = $info->getSaveName();  //获取文件名
                $file_name = ROOT_PATH . 'public' . DS . $exclePath;//上传文件的地址
                $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
                $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8

                $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
                array_shift($excel_array);  //删除第一个数组(标题);
                $data = [];

                foreach($excel_array as $k=>$v) {
                    $established_point = $this->established($v[12]);
                    $sale_point = $this->sale($v[6]);
                    $pnumber_point = $this->pnumber($v[8]);
                    $random = rand(0,30)*10;
                    $score = $established_point + $sale_point +$pnumber_point + $random;

                    $data[$k]['teacase'] = $v[1];
                    $data[$k]['teaarea'] = $v[2];
                    $data[$k]['bangdan'] = $v[3];
                    $data[$k]['name'] = $v[4];
                    $data[$k]['relatedwords'] = $v[5];
                    $data[$k]['sale'] = $v[6];
                    $data[$k]['company'] = $v[7];
                    $data[$k]['pnumber'] = $v[8];
                    $data[$k]['contact'] = $v[9];
                    $data[$k]['phone'] = $v[10];
                    $data[$k]['wechat'] = $v[11];
                    $data[$k]['detailintro'] = $v[12];
                    $data[$k]['score'] = $score;
                 }
                 $retval = model("Pinpai")->insertExcelpp($data);
                 if($retval==1){
                    return $this->success("上传数据成功");
                 }else{
                    return $this->error("上传数据出错");
                 }

            }else
            {
                echo $file->getError();
            }
        }else
        {
            return $this -> fetch();
        }
    }

    //数据导出到Excel
    function exportExcel($teacase, $teaarea)
    {
        $name = date('Y-m-d H-i-s').'.'.'xlsx';
        $path = dirname(__FILE__); //找到当前脚本所在路径
        Loader::import('PHPExcel.Classes.PHPExcel');//手动引入PHPExcel.php
        Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');//引入IOFactory.php文件里面的PHPExcel_IOFactory这个类
        $PHPExcel = new \PHPExcel();//实例化
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle("珍品"); //给当前活动sheet设置名称
        //设置表头
        $PHPSheet->setCellValue("A1","序号");//表格数据
        $PHPSheet->setCellValue("B1","茶类");
        $PHPSheet->setCellValue("C1","区域");
        $PHPSheet->setCellValue("D1","榜单");
        $PHPSheet->setCellValue("E1","品牌名");
        $PHPSheet->setCellValue("F1","品牌关键词");
        $PHPSheet->setCellValue("G1","品牌零售销售额");
        $PHPSheet->setCellValue("H1","公司名称");
        $PHPSheet->setCellValue("I1","公众号名称");
        $PHPSheet->setCellValue("J1","联系人");
        $PHPSheet->setCellValue("K1","电话");
        $PHPSheet->setCellValue("L1","微信");
        $PHPSheet->setCellValue("M1","详细介绍（300~500字）");

        // $teacase, $teaarea
        $tid_name = teacase_ids();
        $aid_name = teaarea_ids();
        $rank = 1;
        if($teacase == 10000){
            $retval =  model('Pinpai')->getAllpp();
            foreach ($retval as &$r) {
                $r['rank'] = $rank;
                $rank++;
                $r['teacase'] = $tid_name[$r['teacase_id']];
                $r['teaarea'] = $aid_name[$r['teaarea_id']];
            }
        }else if($teacase == 9999){
            $retval =  model('Pinpai')->getOther();
            foreach ($retval as &$r) {
                $r['rank'] = $rank;
                $rank++;
                $r['teacase'] = $tid_name[$r['teacase_id']];
                $r['teaarea'] = $aid_name[$r['teaarea_id']];
            }
        }else{
            if($teaarea==10000){
                //取某茶类全部
                $data = ['teacase_id'=>$teacase];
                $retval = getChoose("pinpai", $data);//参数1为表单名称，$data为查询条件
                foreach ($retval as &$r) {
                    $r['rank'] = $rank;
                    $rank++;
                    $r['teacase'] = $tid_name[$r['teacase_id']];
                    $r['teaarea'] = $aid_name[$r['teaarea_id']];
                }
            }else if($teaarea==9999){
                //取未上榜
                $data = ['bangdan_id'=>4];
                $retval = getChoose("pinpai", $data);//参数1为表单名称，$data为查询条件
                foreach ($retval as &$r) {
                    $r['rank'] = $rank;
                    $rank++;
                    $r['teacase'] = $tid_name[$r['teacase_id']];
                    $r['teaarea'] = $aid_name[$r['teaarea_id']];
                }
            }else{
                //取某茶类某产区
                $data = [
                    'teacase_id'=>$teacase,
                    'teaarea_id'=> $teaarea
                ];
                $retval = getChoose("pinpai", $data);//参数1为表单名称，$data为查询条件
                foreach ($retval as &$r) {
                    $r['rank'] = $rank;
                    $rank++;
                    $r['teacase'] = $tid_name[$r['teacase_id']];
                    $r['teaarea'] = $aid_name[$r['teaarea_id']];
                }
            }
        }

        $i=2;
        foreach ($retval as $ret) {
            $PHPSheet->setCellValue("A".$i,$ret['id']);//表格数据
            $PHPSheet->setCellValue("B".$i,$ret['teacase']);
            $PHPSheet->setCellValue("C".$i,$ret['teaarea']);
            $PHPSheet->setCellValue("D".$i,"品牌百科");
            $PHPSheet->setCellValue("E".$i,$ret['name']);
            $PHPSheet->setCellValue("F".$i,$ret['relatedwords']);
            $PHPSheet->setCellValue("G".$i,$ret['sale']);
            $PHPSheet->setCellValue("H".$i,$ret['company']);
            $PHPSheet->setCellValue("I".$i,$ret['pnumber']);
            $PHPSheet->setCellValue("J".$i,$ret['contact']);
            $PHPSheet->setCellValue("K".$i,$ret['phone']);
            $PHPSheet->setCellValue("L".$i,$ret['wechat']);
            $PHPSheet->setCellValue("M".$i,$ret['detailintro']);
            $i++;
        }

        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");//创建生成的格式
        header("Content-Disposition: attachment;filename=$name");//下载下来的表格名
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
    }


    //返回某茶类对应的产地JSON数据
    function reTeaAreaJson($teacase_id)
    {
        // $data = $this->reTeaArea($teacase_id);
        $data = $this->reTeaArea($teacase_id);
        return json_encode($data);
    }


    //返回某茶类对应的产地
    function reTeaArea($teacase_id)
    {
        $retval = model("Pinpai")->reTeaArea($teacase_id);
        $areas = getTeaArea();
        $data = [];
        foreach ($retval as $ret) {
            foreach ($areas as $area) {
                if($ret["teaarea_id"]==$area["id"]){
                    $data[] = $area;
               }
           }
       }
       return $data;
    }

    /*****************************************************************/
    //获取年限分数
    function established($detailintro)
    {
        $cyear = 0;
        $pattern = '/成立于(\d{4})/';
        preg_match_all($pattern, $detailintro, $year);
         // print_pre($year);
        if($year[1]){
            $now = date("Y");
            $cyear = $now - $year[1][0];
        }else{
            $pattern = '/(\d{4})/';
            preg_match_all($pattern, $detailintro, $year);
            if($year[1]){
                $now = date("Y");
                $cyear = $now - $year[1][0];
                if($cyear<0){
                    $cyear = 0;
                }
            }
        }

        if($cyear <= 5){
            $point = $cyear * 200;
        }else if($cyear<=10){
            $point = 1000 + ($cyear-5) * 300;
        }else if($cyear<=15){
            $point = 2500 + ($cyear-10) * 500;
        }else if($cyear<=20){
            $point = 5000 + ($cyear-15) * 800;
        }else{
            $point = 9000;
        }
        return $point;
    }

    //零售额
    function sale($sale)
    {
        $pattern = '/100万/';
        if(preg_match($pattern, $sale)){
            $sale_point = 100;
            return $sale_point;
        }

        $pattern = '/100~300万元/';
        if(preg_match($pattern, $sale)){
            $sale_point = 250;
            return $sale_point;
        }

        $pattern = '/300~1000万元/';
        if(preg_match($pattern, $sale)){
            $sale_point = 500;
            return $sale_point;
        }

        $pattern = '/1000~3000万元/';
        if(preg_match($pattern, $sale)){
            $sale_point = 800;
            return $sale_point;
        }

        $pattern = '/3000~10000万元/';
        if(preg_match($pattern, $sale)){
            $sale_point = 1500;
            return $sale_point;
        }

        $pattern = '/1~3亿/';
        if(preg_match($pattern, $sale)){
            $sale_point = 2000;
            return $sale_point;
        }

        $pattern = '/3亿/';
        if(preg_match($pattern, $sale)){
            $sale_point = 3000;
            return $sale_point;
        }
        return 0;
    }

    //公众号得分
    function pnumber($pnumber)
    {
        if($pnumber){
            return 300;
        }
    }









}


?>